from sqlite3 import OperationalError
import database
from rich.table import Table


POWER_FACTORS = {
    "SOLVED": 10,
    "SOLVED_PERCENTAGE": 1000
}

SQL_FIELDS_EXT = {
    '战力值': f"solved*{POWER_FACTORS['SOLVED']}+{POWER_FACTORS['SOLVED_PERCENTAGE']}*solved/submits as 战力值",
    '通过率': "round(1.0*solved/submits, 3) as 通过率",
}


SQL_DEFAULT_FIELDS_RANK = [
    "username as 姓名", 'id as ID','solved as 已解决', 'submits-solved as 错误', 'submits as 总提交', 
    SQL_FIELDS_EXT['通过率'], SQL_FIELDS_EXT['战力值'], 'teacher as BOSS', 'datetime(updated_at, "localtime") as 更新时间'
]

SQL_DEFAULT_FIELDS_USER = [
    "rank as 排名", 
    "username as 姓名", 'id as ID','solved as 已解决', 'submits-solved as 错误', 'submits as 总提交', 
    SQL_FIELDS_EXT['通过率'], SQL_FIELDS_EXT['战力值'], 'teacher as BOSS', 'datetime(updated_at, "localtime") as 更新时间'
]


cur = database.CONNECTION.cursor()
cur.execute('''
    CREATE TABLE IF NOT EXISTS nbdp_junior(
        'id' VARCHAR(32) NOT NULL PRIMARY KEY,        
        'solved' INT DEFAULT 0,
        'submits' INT DEFAULT 0,
        'updated_at' TIMESTAMP DEFAULT CURRENT_TIMESTAMP   

        'username' VARCHAR(32) NOT NULL,
        FOREIGN KEY(username) REFERENCES users(username),
    )
''')



class NBDP:
    def __init__(self):
       pass

# u = Junior('caorui')
class Junior(NBDP):
    USER_INFO_URL = "http://begin.nbdp.net/oj/userinfo.php?user={}"

    def __init__(self, uid):
        super().__init__()
        self.uid = uid
        
    
    def load_data_from_db(self, fields=SQL_DEFAULT_FIELDS_USER):
        sql = f'''
            SELECT {fields}
            FROM nbdp_junior AS A 
            JOIN (
                SELECT COUNT(*) FROM nbdp_junior AS B
                WHERE B.{SQL_FIELDS_EXT['战力值']}>A.{SQL_FIELDS_EXT['战力值']}
            )
            WHERE A.uid={self.uid}
        '''
        cur = database.CONNECTION.cursor()

        keys = [f if 'as' not in f else f.split('as')[1].strip() for f in fields]

        try:
            values = cur.execute(sql)
        except OperationalError as e:
            print(e)
            return
        
        self.data = dict(zip(keys, values))


    def show_data(self, fields=SQL_DEFAULT_FIELDS_USER, update=False):
        if self.data is None:
            return
        
        columns = [f if 'as' not in f else f.split('as')[1].strip() for f in fields]
        table = Table()
        for k in columns:
            table.add_column(k, justify="center", style="cyan", no_wrap=True)
        
        table.add_row(*self.data)
        print(table)


    @classmethod
    def get_junior_by_username(username):
        sql = f'''
            SELECT * FROM nbdp_junior
            WHERE username={username}
        '''
        cur = database.CONNECTION.cursor()
        try:
            row = cur.execute(sql).fetchone()
        except OperationalError as e:
            print(e)
            return

        return Junior(row[0])


    @classmethod
    def show_user_rank(cls, fields=SQL_DEFAULT_FIELDS_RANK, orderby="战力值", update=False):
        sql = f'''
            SELECT {fields} 
            FROM nbdp_junior
            ORDER BY {orderby}
        '''

        cur = database.CONNECTION.cursor()
        try:
            rows = cur.execute(sql)
        except OperationalError as e:
            print(e)
            return

        columns = [f if 'as' not in f else f.split('as')[1].strip() for f in fields]
        table = Table()
        table.add_column("排名", justify="center", style="cyan", no_wrap=True)
        for k in columns:
            table.add_column(k, justify="center", style="cyan", no_wrap=True)

        for i, row in enumerate(rows):
            table.add_row(i, *map(str, row), style="green")
        
        print(table)



class Senior(NBDP):
    pass